<?php
class sis_pinjaman_anggota_model extends MY_Model {
	var $model = 'sis_pinjaman_anggota';
	
	function GetJSON($as_method = '')
	{
		$ls_sql	= "	SELECT	A.ID_PINJAMAN, A.ID_ANGGOTA, TO_CHAR(A.TGL_PERMOHONAN,'DD/MM/YYYY') TGL_PERMOHONAN, A.JML_PINJAMAN, A.LAMA_PINJAMAN, 
							A.PERSEN_BUNGA, A.KETERANGAN, A.KODE_JENIS_PINJAMAN,
							NVL(A.ST_APPROVE,'0') ST_APPROVE, F_SIS_STATUS_PINJAMAN(ID_PINJAMAN) STATUS_APPROVE,
							TO_CHAR(A.TGL_APPROVE,'DD/MM/YYYY') TGL_APPROVE, A.APPROVE_USER, 
							A.ST_TRANSFER, TO_CHAR(A.TGL_TRANSFER,'DD/MM/YYYY') TGL_TRANSFER, A.BUKTI_TRANSFER, 
							A.ST_LUNAS, TO_CHAR(A.TGL_LUNAS,'DD/MM/YYYY') TGL_LUNAS,
							C.FULL_NAME, C.IDENTITY_NUMBER, D.NAMA_JENIS_ANGGOTA, E.NAMA_BANK,
							G.ID_PERUSAHAAN, G.KODE_PERUSAHAAN, G.NAMA_PERUSAHAAN,
							F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'POKOK', SYSDATE) SIMPANAN_POKOK,
							F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'WAJIB', SYSDATE) SIMPANAN_WAJIB,
							F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'SUKARELA', SYSDATE) SIMPANAN_SUKARELA
					FROM	SIS_PINJAMAN_ANGGOTA A
							LEFT JOIN SIS_ANGGOTA B ON A.ID_ANGGOTA = B.ID_ANGGOTA
							LEFT JOIN SW_PERSON C ON B.PERSON_ID = C.PERSON_ID
							LEFT JOIN SIS_SETUP_JENIS_ANGGOTA D ON B.KD_JENIS_ANGGOTA = D.KD_JENIS_ANGGOTA
							LEFT JOIN KEU_BANK E ON E.KD_BANK = A.KD_BANK
							LEFT JOIN SW_PERUSAHAAN G ON B.ID_PERUSAHAAN = G.ID_PERUSAHAAN
					WHERE	(UPPER(NVL(A.ID_PINJAMAN,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(A.ID_ANGGOTA,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(C.FULL_NAME,'')) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(NVL(C.IDENTITY_NUMBER,'')) LIKE '%".strtoupper(post('keyword'))."%') 
							".(post('kd_jenis_anggota') != '' ? " AND B.KD_JENIS_ANGGOTA = '".post('kd_jenis_anggota')."' " : '')."
							
							AND UPPER(NVL(g.id_perusahaan,'')) LIKE '%".strtoupper(post('id_perusahaan'))."%'
							AND UPPER(NVL(g.kode_perusahaan,'')) LIKE '%".strtoupper(post('kode_perusahaan'))."%'
							AND UPPER(NVL(g.nama_perusahaan,'')) LIKE '%".strtoupper(post('nama_perusahaan'))."%'
							
							".($as_method == 'NEW' ? " AND NVL(ST_APPROVE,'0') IN ('0') " : '')."
							".($as_method == 'APPROVAL' ? " AND NVL(ST_APPROVE,'0') IN ('0','1') AND NVL(A.ST_TRANSFER,'0') = '0' " : '')."
					ORDER BY ".(post('sort')=='' ? "NVL(A.ST_APPROVE,'0') ASC, A.TGL_PERMOHONAN" : post('sort'))." ".(post('order')=='' ? 'desc' : post('order'));
					
		return json_encode( $this->paging($ls_sql) );
	}
	
	function GetDetail($param=array())
	{
		
		$ls_sql	= "	SELECT	A.ID_PINJAMAN, A.ID_ANGGOTA, TO_CHAR(A.TGL_PERMOHONAN,'DD/MM/YYYY') TGL_PERMOHONAN, A.JML_PINJAMAN, A.LAMA_PINJAMAN, 
							A.PERSEN_BUNGA, A.KETERANGAN, A.KODE_JENIS_PINJAMAN,
							A.ST_APPROVE, F_SIS_STATUS_PINJAMAN(ID_PINJAMAN) STATUS_APPROVE,
							TO_CHAR(A.TGL_APPROVE,'DD/MM/YYYY') TGL_APPROVE, A.APPROVE_USER, 
							A.ST_TRANSFER, TO_CHAR(A.TGL_TRANSFER,'DD/MM/YYYY') TGL_TRANSFER, A.BUKTI_TRANSFER, 
							A.ST_LUNAS, TO_CHAR(A.TGL_LUNAS,'DD/MM/YYYY') TGL_LUNAS,
							C.FULL_NAME, D.KD_JENIS_ANGGOTA, D.NAMA_JENIS_ANGGOTA, E.NAMA_BANK
					FROM	SIS_PINJAMAN_ANGGOTA A
							LEFT JOIN SIS_ANGGOTA B ON A.ID_ANGGOTA = B.ID_ANGGOTA
							LEFT JOIN SW_PERSON C ON B.PERSON_ID = C.PERSON_ID
							LEFT JOIN SIS_SETUP_JENIS_ANGGOTA D ON B.KD_JENIS_ANGGOTA = D.KD_JENIS_ANGGOTA
							LEFT JOIN KEU_BANK E ON E.KD_BANK = A.KD_BANK
					WHERE	(UPPER(NVL(A.ID_PINJAMAN,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(A.ID_ANGGOTA,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(A.ID_PINJAMAN,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(B.GENDER_NAME,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(A.FULL_NAME,'')) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(NVL(A.MOTHER_NAME,'')) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(NVL(A.IDENTITY_NUMBER,'')) LIKE '%".strtoupper(post('keyword'))."%') 
					ORDER BY NVL(A.ST_APPROVE,'0') ASC, A.TGL_PERMOHONAN";

		$result = $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			return $result->result_array();
		}else{
			return NULL;
		}
	}
	
	function Add($arr_data){
		$this->db->insert('SIS_PINJAMAN_ANGGOTA', $arr_data);
	}
	
	function Update($arr_data, $arr_rule){
		$this->db->update('SIS_PINJAMAN_ANGGOTA', $arr_data, $arr_rule);
	}
	
	function GetSingle($as_idpinjaman){
		$result = $this->db->get_where('SIS_PINJAMAN_ANGGOTA', array('ID_PINJAMAN' => $as_idpinjaman));
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}
	
	function GetSingleView($as_idpinjaman)
	{
		$result = $this->db->query("SELECT	A.ID_PINJAMAN, A.ID_ANGGOTA, TO_CHAR(A.TGL_PERMOHONAN,'DD/MM/YYYY') TGL_PERMOHONAN, A.JML_PINJAMAN, A.LAMA_PINJAMAN, 
											A.PERSEN_BUNGA, A.KETERANGAN, A.KODE_JENIS_PINJAMAN, A.ST_APPROVE, F_SIS_STATUS_PINJAMAN(ID_PINJAMAN) STATUS_APPROVE,
											TO_CHAR(A.TGL_APPROVE,'DD/MM/YYYY') TGL_APPROVE, A.APPROVE_USER, 
											A.ST_TRANSFER, TO_CHAR(A.TGL_TRANSFER,'DD/MM/YYYY') TGL_TRANSFER, A.BUKTI_TRANSFER, 
											A.ST_LUNAS, TO_CHAR(A.TGL_LUNAS,'DD/MM/YYYY') TGL_LUNAS,
											C.FULL_NAME, D.KD_JENIS_ANGGOTA, D.NAMA_JENIS_ANGGOTA, A.KD_BANK, E.NAMA_BANK, F.NAMA_JENIS_PINJAMAN,
                                            A.NAMA_CABANG, A.NO_REKENING, A.PEMILIK_REKENING
									FROM	SIS_PINJAMAN_ANGGOTA A
											LEFT JOIN SIS_ANGGOTA B ON A.ID_ANGGOTA = B.ID_ANGGOTA
											LEFT JOIN SW_PERSON C ON B.PERSON_ID = C.PERSON_ID
											LEFT JOIN SIS_SETUP_JENIS_ANGGOTA D ON B.KD_JENIS_ANGGOTA = D.KD_JENIS_ANGGOTA
											LEFT JOIN KEU_BANK E ON E.KD_BANK = A.KD_BANK
											LEFT JOIN SIS_SETUP_JENIS_PINJAMAN F ON F.KODE_JENIS_PINJAMAN = A.KODE_JENIS_PINJAMAN
									WHERE	ID_PINJAMAN = '".$as_idpinjaman."'");
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}

	function DeleteSingle($arr_rule)
	{
		try {
			if(	intval($this->get_data("SELECT COUNT(1) FROM SIS_PINJAMAN_ANGGOTA WHERE ID_PINJAMAN = '".$arr_rule['id_pinjaman']."' AND NVL(ST_APPROVE,'0') <> '0'"))>0)
			{
				return $this->error_delete.' [id: '.$arr_rule['id_pinjaman']."]<br />";
			}else{
				$this->db->delete('SIS_PINJAMAN_ANGGOTA', array('ID_PINJAMAN' => $arr_rule['id_pinjaman']));
				return '';
			}
		} catch(Exception $e) {
			return $e->getMessage();
		}
	}
}
?>